from . import db, login_manager
from flask import current_app, url_for
from flask_login import UserMixin, AnonymousUserMixin
from sqlalchemy.exc import IntegrityError
from sqlalchemy import text, event
from decimal import Decimal
from datetime import datetime
# from itsdangerous import TimedJSONWebSignatureSerializer as Serializer
from werkzeug.security import generate_password_hash, check_password_hash
import base64

# 计算时间d距离now()的长度，比如：1分钟前，1小时前，1月前，1年前
def timebefore(d):
    chunks = (
                       (60 * 60 * 24 * 365, '年'),
                       (60 * 60 * 24 * 30, '月'),
                       (60 * 60 * 24 * 7, '周'),
                       (60 * 60 * 24, '天'),
                       (60 * 60, '小时'),
                       (60, '分钟'),
    )
    
    now = datetime.now()
    delta = now - d
    #忽略毫秒  
    before = delta.days * 24 * 60 * 60 + delta.seconds  #python2.7直接调用 delta.total_seconds()  
    #刚刚过去的1分钟  
    if before <= 60:  
           return '刚刚'  
    for seconds,unit in chunks:
           count = before // seconds
           if count != 0:
                 break
    return str(count)+unit+"前"


# class advertisement --> table advertisement
class advertisement(db.Model):
    __tablename__ = "advertisement"
    adv_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    img_url = db.Column(db.String(200), nullable=False)
    redirect_url = db.Column(db.String(200), nullable=False)
    create_date = db.Column(db.DateTime)
    is_used = db.Column(db.Boolean, default=False)

    def __repr__(self):
        return '<advertisement: %r>' % self.adv_id

    def to_json(self):
        json_advertisement = {
            'adv_id': self.adv_id,
            'img_url': self.img_url,
            'redirect_url': self.redirect_url,
            'create_date': str(self.create_date).split('.')[0],
            'is_used': self.is_used
        }
        return json_advertisement


# class User --> table users
class User(UserMixin, db.Model):
    __tablename__ = 'users'
    user_id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 用户的id
    openId = db.Column(db.String(50), unique=True, nullable=False) # 微信用户唯一表示
    nickName = db.Column(db.String(50), nullable=False) # 昵称
    gender = db.Column(db.Integer) # 性别，1为男，0为女
    city = db.Column(db.String(40)) # 所在城市
    avatarUrl = db.Column(db.String(200)) # 头像链接
    personal_sign = db.Column(db.String(50)) # 个性签名
    height = db.Column(db.Integer) # 身高，以cm为单位如 173
    school = db.Column(db.String(50)) # 学校名称
    grade = db.Column(db.String(50)) # 年级如2016
    constellation = db.Column(db.String(10)) # 星座 双子座、处女座、天蝎座
    hobby = db.Column(db.String(50)) # 爱好 -> 吃火锅-蹦迪-帅哥 限六个字
    fans_count = db.Column(db.Integer, default=0)  # 粉丝数 --> 此处应当建立触发器
    follows_count = db.Column(db.Integer, default=0)  # 关注数 --> 此处应当建立触发器
    trends_count = db.Column(db.Integer, default=0)   # 发布的动态数

    user_trends = db.relationship('userTrends', backref='user', lazy='dynamic')
    user_collection = db.relationship('userCollection', backref='user', lazy='dynamic')
    follows = db.relationship('follows', backref='user', lazy='dynamic')
    message_list = db.relationship('messageList', backref='user', lazy='dynamic')

    anonymous_wall = db.relationship('anonymousWall', backref='user', lazy='dynamic')
    anonymous_wall_like = db.relationship('anonymousWallLike', backref='user', lazy='dynamic')
    love_post = db.relationship('lovePost', backref='user', lazy='dynamic')
    user_love_post_like = db.relationship("userLovePostLike", backref="user", lazy='dynamic')
    love_comment = db.relationship('loveComment', backref='user', lazy='dynamic')
    user_love_comment_like = db.relationship("userLoveCommentLike", backref="user", lazy='dynamic')


    def __repr__(self):
        return '<User %r>' % self.nickName

    def get_id(self):
        return str(self.user_id)

    # 序列化转换: 对象->JSON
    def to_json(self):
        json_user = {
            'user_id': self.user_id,
            'nickName': self.nickName,
            'gender': self.gender,
            'city': self.city,
            'school': self.school,
            'grade': self.grade,
            'avatarUrl': self.avatarUrl,
            'personal_sign': self.personal_sign,
            'fans_count': self.fans_count,
            'follows_count': self.follows_count,
            'trends_count': self.trends_count
        }
        return json_user

    # 转换json格式的个人详细介绍
    def to_json_personal_detail(self):
        json_user_detail = {
            'user_id': self.user_id,
            'nickName': self.nickName,
            'gender': self.gender,
            'height': self.height if self.height else '',
            'school': self.school,
            'grade': self.grade,
            'constellation': self.constellation if self.constellation else '',
            'hobby': self.hobby if self.hobby else '',
            'avatarUrl': self.avatarUrl,
            'personal_sign': self.personal_sign,
            'fans_count': self.fans_count,
            'follows_count': self.follows_count,
            'trends_count': self.trends_count
        }
        return json_user_detail


# user_loader回调，用于从会话存储中的用户id重新加载用户对象
@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))


# class AnonymousUser --> no table
class AnonymousUser(AnonymousUserMixin):
  def can(self, permissions):
    return False

  def is_administrator(self):
    return False

login_manager.anonymous_user = AnonymousUser


# class follows --> table follows
class follows(db.Model):
    __tablename__ = "follows"
    # follow_id = db.Column(db.Integer, unique=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    fans_id = db.Column(db.Integer, primary_key=True) # 粉丝的ID -- 参照user_id

    def __repr__(self):
        return '<userFollows %r>' % self.user_id

    def to_json(self):
        json_follow = {
            # 'follow_id': self.follow_id,
            'user_id': self.user_id,
            'fans_id': self.fans_id
        }
        return json_follow



# class userTrends  --> table user_trends
class userTrends(db.Model):
    __tablename__ = "user_trends"
    trends_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    love_post_id = db.Column(db.String(100), db.ForeignKey('love_post.love_post_id', ondelete='CASCADE'))

    def __repr__(self):
        return '<Trends_id: %r>' % self.trends_id

    def to_json(self):
        json_user_trend = {
            'trends_id': self.trends_id,
            'user_id': self.user_id,
            'love_post_id': self.love_post_id
        }
        return json_user_trend


# class userCollection --> table user_collection
class userCollection(db.Model):
    __tablename__ = "user_collection"
    # user_collect_id = db.Column(db.Integer, unique=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    love_post_id = db.Column(db.String(100), db.ForeignKey('love_post.love_post_id', ondelete='CASCADE'), primary_key=True)

    def __repr__(self):
        return '<userCollection: %r>' % self.user_collect_id

    def to_json(self):
        json_user_collection = {
            # 'user_collect_id': self.user_collect_id,
            'user_id': self.user_id,
            'love_post_id': self.love_post_id
        }
        return json_user_collection


# 消息列表 -- 记录通知用户的所有消息
class messageList(db.Model):
    __tablename__ = "message_list"
    message_id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 消息id，自行增长
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id')) # 外键，参照user_id
    message_creator = db.Column(db.Integer) # 产生这个消息的用户id
    # 通知类型：新增粉丝addFans/被回复reviewed(表白墙或评论)/评论被点赞commentLiked/表白墙被喜欢lovePostLiked
    message_type = db.Column(db.String(20), nullable=False) 
    refer_id = db.Column(db.String(100)) # 目标id（引导使用）
    create_time = db.Column(db.DateTime) # 发布时间

    def __repr__(self):
        return '<messageList: %r>' % self.message_id

    def to_json(self):
        json_message_list = {
            'message_id': self.message_id,
            'user_id': self.user_id,
            'message_type': self.message_type,
            'refer_id': self.refer_id,
            'create_time': timebefore(self.create_time),
            'message_creator': self.message_creator
        }
        return json_message_list


# 匿名墙
class anonymousWall(db.Model):
    __tablename__ = "anonymous_wall"
    anonymous_wall_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    content = db.Column(db.Text) # 内容
    like = db.Column(db.Integer, default=0) # 喜欢数
    review = db.Column(db.Integer, default=0) # 评论数 --> 此处应当建立触发器

    anonymous_wall_like = db.relationship("anonymousWallLike", backref="anonymouswall", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    anonymous_wall_comment = db.relationship("anonymousWallComment", backref="anonymouswall", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)

    def __repr__(self):
        return '<anonymousWall: %r>' % self.anonymous_wall_id

    # 将资源转化为json返回
    def to_json(self):
        json_anonymous_wall = {
            'anonymous_wall_id': self.anonymous_wall_id,
            'user_id': self.user_id,
            'content': self.content,
            'like': self.like,
            'review': self.review
        }
        return json_anonymous_wall

    # 将json转化为资源对象
    @staticmethod
    def from_json(json_anonymous_wall):
        user_id = json_anonymous_wall.get('user_id')
        content = json_anonymous_wall.get('content')
        return anonymousWall(user_id=user_id, content=content)


# 匿名墙喜欢表
class anonymousWallLike(db.Model):
    __tablename__ = "anonymous_wall_like"
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    anonymous_wall_id = db.Column(db.Integer, db.ForeignKey('anonymous_wall.anonymous_wall_id', ondelete='CASCADE'), primary_key=True)

    def __repr__(self):
        return '<anonymousWallLike: %r>' % self.anonymous_wall_id

    def to_json(self):
        json_like = {
            'user_id': self.user_id,
            'anonymous_wall_id': self.anonymous_wall_id
        }
        return json_like


# 匿名墙评论
class anonymousWallComment(db.Model):
    __tablename__ = "anonymous_wall_comment"
    anonymous_wall_comment_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    anonymous_wall_id = db.Column(db.Integer, db.ForeignKey("anonymous_wall.anonymous_wall_id", ondelete='CASCADE'))
    content = db.Column(db.String(500)) # 内容
    publish_time = db.Column(db.DateTime) # 发布时间

    def __repr__(self):
        return '<anonymousWallComment: %r>' % self.anonymous_wall_comment_id

    def to_json(self):
        json_anonymous_wall_comment = {
            'anonymous_wall_comment_id': self.anonymous_wall_comment_id,
            'anonymous_wall_id': self.anonymous_wall_id,
            'content': self.content,
            'publish_time': timebefore(self.publish_time)
        }
        return json_anonymous_wall_comment

    # 生成对象
    @staticmethod
    def from_json(json_anonymous_wall_comment):
        anonymous_wall_id = json_anonymous_wall_comment.get('anonymous_wall_id')
        content = json_anonymous_wall_comment.get('content')

        return anonymousWallComment(anonymous_wall_id=anonymous_wall_id, content=content)



# class lovePost --> table love_post
class lovePost(db.Model):
    __tablename__ = "love_post"
    # __table_args__ = {'mysql_collate': 'utf8mb4_bin'}
    love_post_id = db.Column(db.String(100), primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    media_type = db.Column(db.String(10), nullable=False) # 内容类型：img/video/text
    tag = db.Column(db.String(100))  # 标签 以空格作为分割符
    describe = db.Column(db.Text)
    publish_time = db.Column(db.DateTime)
    like = db.Column(db.Integer, default=0) # 喜欢数
    review = db.Column(db.Integer, default=0) # 评论数 --> 此处应当建立触发器

    user_trends = db.relationship("userTrends", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    user_collection = db.relationship("userCollection", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    user_love_post_like = db.relationship("userLovePostLike", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    love_comment = db.relationship("loveComment", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    images = db.relationship("image", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)
    videos = db.relationship("video", backref="lovepost", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)

    def __repr__(self):
        return '<lovePost: %r>' % self.love_post_id

    # 将资源转化为json返回
    def to_json(self):
        json_love_post = {
            'love_post_id': self.love_post_id,
            'user_id': self.user_id,
            'media_type': self.media_type,
            'tag': self.tag.split() if self.tag else '',
            'describe': self.describe,
            'publish_time': timebefore(self.publish_time),
            'like': self.like,
            'review': self.review
        }
        return json_love_post

    # 将json转化为资源对象
    @staticmethod
    def from_json(json_love_post):
        love_post_id = json_love_post.get('love_post_id')
        user_id = json_love_post.get('user_id')
        media_type = json_love_post.get('media_type')
        describe = json_love_post.get('describe')
        tag = json_love_post.get('tag')
        return lovePost(love_post_id=love_post_id, user_id=user_id, media_type=media_type, describe=describe, tag=tag)



# class userLovePostLike --> table user_love_post_like
class userLovePostLike(db.Model):
    __tablename__ = "user_love_post_like"
    # user_love_post_like_id = db.Column(db.Integer, unique=True, autoincrement=True)/
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    love_post_id = db.Column(db.String(100), db.ForeignKey('love_post.love_post_id', ondelete='CASCADE'), primary_key=True)

    def __repr__(self):
        return '<userLovePostLike: %r>' % self.user_love_post_like_id


# class loveComment --> table love_comment
class loveComment(db.Model):
    __tablename__ = "love_comment"
    love_post_comment_id = db.Column(db.String(100), primary_key=True)
    love_post_id = db.Column(db.String(100), db.ForeignKey("love_post.love_post_id", ondelete='CASCADE'))
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
    reply_user_id = db.Column(db.Integer) # 默认是楼主的ID
    content = db.Column(db.String(500)) # 内容
    publish_time = db.Column(db.DateTime) # 发布时间
    like = db.Column(db.Integer, default=0) # 喜欢数

    user_love_comment_like = db.relationship("userLoveCommentLike", backref="lovecomment", lazy='dynamic', cascade='all, delete-orphan', passive_deletes=True)

    def __repr__(self):
        return '<loveComment: %r>' % self.love_post_comment_id

    def to_json(self):
        json_love_comment = {
            'love_post_comment_id': self.love_post_comment_id,
            'love_post_id': self.love_post_id,
            'user_id': self.user_id,
            'reply_user_id': self.reply_user_id,
            'content': self.content,
            'publish_time': timebefore(self.publish_time),
            'like': self.like
        }
        return json_love_comment

    # 生成对象
    @staticmethod
    def from_json(json_love_comment):
        love_post_comment_id = json_love_comment.get('love_post_comment_id')
        love_post_id = json_love_comment.get('love_post_id')
        user_id = json_love_comment.get('user_id')
        reply_user_id = json_love_comment.get('reply_user_id')
        content = json_love_comment.get('content')

        return loveComment(love_post_comment_id=love_post_comment_id, love_post_id=love_post_id, user_id=user_id, reply_user_id=reply_user_id, content=content)


# class userLoveCommentLike --> table user_love_comment_like
class userLoveCommentLike(db.Model):
    __tablename__ = "user_love_comment_like"
    # user_love_comment_like_id = db.Column(db.Integer, unique=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    love_post_comment_id = db.Column(db.String(100), db.ForeignKey('love_comment.love_post_comment_id', ondelete='CASCADE'), primary_key=True)

    def __repr__(self):
        return '<userLoveCommentLike: %r>' % self.user_love_comment_like_id


# class images --> table images
class image(db.Model):
    __tablename__ = "images"
    img_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    love_post_id = db.Column(db.String(100), db.ForeignKey("love_post.love_post_id", ondelete='CASCADE'))
    img_url = db.Column(db.String(200), nullable=False)
    sequence_num = db.Column(db.Integer, default=0)

    def __repr__(self):
        return '<images: %r>' % self.img_id


# class video --> table videos
class video(db.Model):
    __tablename__ = "videos"
    video_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    love_post_id = db.Column(db.String(100), db.ForeignKey("love_post.love_post_id", ondelete='CASCADE'))
    aliyun_video_id = db.Column(db.String(100))

    def __repr__(self):
        return '<videos: %r>' % self.video_id


# class admin --> table admin
class admin(db.Model):
    __tablename__ = "admin"
    adminId = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(30), unique=True, nullable=False)
    password = db.Column(db.String(50), unique=True, nullable=False) # md5加密后的密文
    permission = db.Column(db.String(20), default="readOnly") # 权限设置，readOnly只读，full所有权限，full可以赋予权限

    def __repr__(self):
        return '<admin: %r>' % self.adminId

    # 生成加盐的密码
    @staticmethod
    def setPassword(password):
        return generate_password_hash(password)

    def checkPassword(self, password):
        return check_password_hash(self.password, password)